crossref <- check_variable("BeginningYearBalanceAmt", data = endowment_data)
plot_missing("CYBeginningYearBalanceAmt")

# stacked chart, note we can't see how nonzero counts are changing 
# relative to the total counts

crossref %>%
  select(EIN, contains("difference"), fiscal_year) %>%
  pivot_longer(cols = contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(name)  %>%
  summarize(zero = sum(ifelse(value == 0, 1,0)),
            nonzero = sum(ifelse(value == 0, 0,1))) %>%
  # notice each row represents a fiscal_year-EIN-difference_type 
  pivot_longer(cols = c(zero, nonzero),
               names_to = "source",
               values_to = "count") %>%
  mutate(name = gsub("difference_in_reported_year", "", name),
         source = ifelse(source == "nonzero",
                         "Nonzero Difference", 
                         "Zero Difference")) %>%
  ggplot(aes(x=name, y = count, fill = source)) +
  geom_bar(stat ="identity", position = "stack", alpha = .8) +
  geom_label(aes(label = round(count,2), y = count, color = source),
             position = "stack",
             size = 2.6,
             label.padding = unit(.1, "lines"),
             fill = "white",
             fontface="bold",
             show.legend = FALSE) +
  labs(title = "Number of Zero and Nonzero Differences\nBetween Cross Referenced Reports",
       subtitle = "By Year",
       x = "Years Between Reports Compared",
       y = "Count",
       fill = "") +
  theme_bw() +
  theme(plot.title = element_text(size = 16, hjust = .5, face="bold"),
        plot.subtitle = element_text(hjust = .5, face="italic"),
        axis.text.x = element_text(size = 13),
        axis.title = element_text(size = 16, face = "bold"))

# plot fraction where there was a difference between 
# the reports by year
crossref %>% 
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(name)  %>%
  summarize(number_zeros = sum(ifelse(value == 0, 1,0)),
            total_reports = n(),
            fraction = 1-( number_zeros / total_reports)) %>%
  mutate(name = gsub("difference_in_reported_year", "", name)) %>%
  ggplot(aes(x=name, y = fraction)) +
  geom_bar(stat ="identity", fill = "#234A77") +
  geom_label(aes(label = round(fraction,2))) +
  labs(title = paste0("Fraction of Differences that Were Nonzero\n",
                      "Between Cross Referenced Reports"),
       subtitle = "By Year",
       x = "Years Between Reports Compared",
       y = "Fraction with Nonzero Difference") +
  theme_bw() +
  theme(plot.title = element_text(hjust = .5, face="bold"),
        plot.subtitle = element_text(hjust = .5, face="italic"))

# plot counts
crossref %>%
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(name)  %>%
  summarize(zero = sum(ifelse(value == 0, 1,0)),
            nonzero = sum(ifelse(value == 0, 0,1))) %>%
  pivot_longer(cols = c(zero, nonzero),
               names_to = "source",
               values_to = "count") %>%
  mutate(name = gsub("difference_in_reported_year", "", name)) %>%
  ggplot(aes(x=name, y = count, fill = source)) +
  geom_bar(stat ="identity", position = "stack") +
  geom_label(aes(label = round(count,2), y = count),
             position = "stack",
             size = 2.6,
             label.padding = unit(.1, "lines"),
             show.legend = FALSE) +
  labs(title = "EINs with Zero and Nonzero Difference\nBetween Cross Referenced Reports",
       subtitle = "By Year",
       x = "Years Between Reports Compared",
       y = "Count") +
  theme_bw() +
  theme(plot.title = element_text(size = 16, hjust = .5, face="bold"),
        plot.subtitle = element_text(hjust = .5, face="italic"),
        axis.text.x = element_text(size = 13),
        axis.title = element_text(size = 16, face = "bold"))

# difference represents What They Reported as CY Minus X Years - What They Reported at The Time

companies_different <- crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  filter(value > 0) %>%
   left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  arrange(organization_name) %>%
  pull(EIN) %>%
  unique()
  
crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  filter(value > 0) %>%
  left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  mutate(year = substr(name, nchar(name), nchar(name)),
         year = paste0("Comparing Current<br> Year Minus ",
                       year)) %>%
  arrange(organization_name) %>%
  select(`Organization Name` = organization_name,
         `Difference in Years` = year, 
         `Fiscal Year` = fiscal_year,
         `Recent  - Previously Reported` = value) %>%
  make_table(title = paste0(
    "Comparing Values Reported in More Recent Report to Those Previously Reported:<br>",
    "<i>Number of Companies that have at Least One Report Not Concordant: </i>",
    length(companies_different)),
             digits = 3, 
             format.args = list(
               big.mark = ",",
               scientific = FALSE),
    escape=FALSE,
    booktabs=TRUE)  %>%
  scroll_box(height = "450px",
             width = "100%") 

# iterate through EINs where there was discordance and
# generate a table so we can better see what's going on

variable_name <- "BeginningYearBalanceAmt"

walk(1:length(companies_different), ~{
  name <- companies_to_ein %>%
    filter(EIN == companies_different[.x]) %>%
    pull(organization_name)
  
  table <- crossref %>% 
    rename_with(cols=everything(), ~gsub(variable_name, "", .)) %>%
    filter(EIN %in% companies_different[.x]) %>%
    select(-c(EIN, contains("difference"))) %>%
    make_table(title = paste0("Reports for ",
                              name, "<br>EIN: ", 
                              companies_different[.x],
                               ", Variable: ", variable_name))
  
  print(table)
  
#  print(table)

})

crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  # filter(value > 0) %>%
  left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  mutate(year = substr(name, nchar(name), nchar(name)),
         year = paste0("Comparing Current Year Minus ",
                       year)) %>%
  arrange(organization_name) %>% View()

variables_to_check  <- endowment_data %>%
  select(contains("CY")) %>%
  colnames() %>%
  gsub("CY|CYM.", "",.) %>%
  unique()

crossref_all <- map_df(
  variables_to_check,
  ~{  variable_name <- .x
  check_variable(variable_name,
                 data = endowment_data) %>% 
    # remove variable name part of column name 
    # so we can bind rows together, add this information
    # as a separate column
    rename_with(cols=everything(), 
                ~gsub(variable_name, "", .)) %>%
    mutate(variable = .x)
})


missing_all <- map_df( variables_to_check, 
 ~ {variable <- paste0("CY",.x)
    endowment_data %>%
      group_by(EIN) %>%
      summarize(number_observations = sum(!is.na(!!sym(variable)))) %>%
      group_by(number_observations) %>%
      summarize(number_eins=n()) %>%
      mutate(variable = variable)
})

colors <- c("#58b5e1", "#49406e", "#9dd84e", "#6633b4", "#46ebdc")


missing_all %>%
  mutate(number_observations = paste0(
    "Number of EINS with ",
    number_observations,
    " Observations for this Variable" )) %>%
      ggplot(aes(x = variable, y =number_eins, fill = variable))+
      geom_bar(stat="identity",
               position = "dodge",
               show.legend=FALSE) +
      geom_label(aes(label = number_eins,
                     color = variable),
                 fill = "white",
                 vjust = .5,
                 size = 2,
                 position = position_dodge(1),
                 label.padding = unit(.1, "lines"),
                 show.legend=FALSE) +
       facet_wrap(~number_observations, ncol=1) +
  coord_flip() +
      labs(y = "Number of Companies",
           x = "Variable Name",
           title = "Comparing Missingness Across Variables") +
      theme_bw() +
      theme(plot.title = element_text(face = "bold", hjust = .5),
            axis.title = element_text(face = "bold")) +
  scale_fill_manual(values = colors) +
  scale_color_manual(values = colors) +
  scale_y_continuous(n.breaks = 8) 

# plot fraction discordant for each variable
crossref_all %>%
  select(EIN, contains("difference"), variable) %>%
  pivot_longer( contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(variable) %>%
  summarize(number_of_discordant_observations = sum(value > 1),
            total_observations_of_variable = n(),
            fraction_discordant = number_of_discordant_observations / total_observations_of_variable) %>%
  ggplot(aes(x = fct_reorder(variable,
                             fraction_discordant,
                             .desc = TRUE),
             y = fraction_discordant)) +
  geom_bar(stat="identity",
           fill = "#234A77")+
  geom_label(aes(label = round(fraction_discordant, 3))) +
  theme_bw() +
  theme(plot.title = element_text(face = "bold", hjust = .5, size = 16),
            axis.title = element_text(face = "bold", size =16),
            axis.text.x = element_text(size = 12, angle = 10, vjust = .6)) +
  labs(y = "Fraction Discordant",
       x = "Endowment Variable",
       title = "Fraction of Observations that Were Discordant for Each Variable")

get_discordant_table <- function(variable_name, data) {
  
    cross_ref_for_var <- data %>%
      filter(variable == variable_name) %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value) %>%
      filter(value > 0) 
    
  discordant <- cross_ref_for_var %>%
    pull(EIN) %>% unique()
  
  
  cross_ref_for_var %>%
    left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
    mutate(year = substr(name, nchar(name), nchar(name)),
           year = paste0("Comparing Current<br> Year Minus ",
                         year)) %>%
    arrange(organization_name) %>%
    select(`Organization Name` = organization_name,
           `Difference in Years` = year, 
           `Fiscal Year` = fiscal_year,
           `Recent  - Previously Reported` = value) %>%
    make_table(title = paste0("Variable: ", 
                              variable_name,
      "<br>Comparing Values Reported in More Recent Report to Those Previously Reported:<br>",
      "<i>Number of Companies that have at Least One Report Not Concordant: </i>",
      length(discordant)),
               digits = 3, 
               format.args = list(
                 big.mark = ",",
                 scientific = FALSE),
      escape=FALSE,
      booktabs=TRUE)  %>%
    scroll_box(height = "450px",
               width = "100%") 

}


walk(variables_to_check, ~{
  table_for_var <- get_discordant_table(.x, data = crossref_all)
  print(table_for_var)
})

intersections <- crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
      group_by(variable) %>%
      summarize(EINs = list(unique(EIN)))

crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
      group_by(EIN) %>%
      summarize(variable = paste(unique(variable), collapse=",<br>")) %>%
  left_join(companies_to_ein) %>%
  arrange(organization_name) %>%
  select(`Organization Name` = `organization_name`,
         `Variables with Discordant Reporting` = variable) %>%
  make_table(
    title = "Companies with Discordant Reporting for at Least One Variable",
    escape=FALSE)

Reduce(intersect, intersections$EINs) %>% length() 

Reduce(union, intersections$EINs) %>% length() 

variable_name <- "BeginningYearBalanceAmt"


plot_reported_for_variable <- function(variable_name, crossref, endowment) {
  
  cross_ref_for_var <- crossref %>%
      filter(variable == variable_name) %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value) %>%
      filter(value > 0) 
    
  discordant <- cross_ref_for_var %>%
      pull(EIN) %>% unique()
  
  number_cols <- ifelse(length(discordant) <= 6, 1,2)
  
  
  # plot the values for the year they correspond to so we can compare,
  # for example, if CM1 for 2016 is the same as CY for 2015
  endowment %>%
    filter(EIN %in% discordant) %>%
    select(EIN, fiscal_year, contains(variable_name)) %>%
    group_by(EIN) %>%
    arrange(fiscal_year) %>% 
    pivot_longer(3: ncol(.)) %>%
    mutate(source = ifelse(grepl("CYM", name), substr(name, 1,4), "CY"),
           year_lag = ifelse(grepl("CYM", name), substr(source, 4,4), 0),
           year_lag = as.numeric(year_lag),
           fiscal_year = as.integer(paste0(fiscal_year))) %>%
    mutate(value_year = fiscal_year -year_lag
           ) %>%
    left_join(companies_to_ein) %>%
    mutate(organization_name = paste0(organization_name, 
                                      " (EIN: ", EIN, ")")) %>%
    ggplot(aes(x = value_year, y = value)) +
    geom_jitter(aes(fill=source), height  =0, 
                width = .2,
                alpha = .8,
                size = 2.2,
                shape =21,
                color = "black",
                stroke =.4) +
   # geom_line(aes(group = source, color = source)) +
    facet_wrap(~organization_name, scales= 'free_y', ncol = number_cols) +
    scale_x_continuous(breaks = 2011:2021 ) +
    scale_y_continuous(labels = comma) +
    viridis::scale_fill_viridis(option="magma", discrete=TRUE) +
    theme_bw() +
    labs(x = "Fiscal Year",
         y = "Reported Value (Dollars)",
         title = paste0("Comparing Reported Values for ", variable_name),
         subtitle = "Only Considering Companies with at Least One Discordant Value") +
    theme(plot.title = element_text(
      face = "bold",
      hjust = .5, 
      size = 16),
      axis.title = element_text(face = "bold", size =16),
      axis.text = element_text(size = 12),
      strip.text = element_text(face = "bold", size = 14),
      plot.subtitle=element_text(size =14, 
                                 face="italic",
                                 hjust = .5),
      legend.text = element_text(size = 10),
      legend.title = element_text(face = "bold", size = 12)) +
    guides(legend = guide_legend(override.aes = list(size = 3)))

}


walk(unique(variables_to_check),
     ~ {plt <- plot_reported_for_variable(
       variable_name = .x,
       crossref = crossref_all,
       endowment = endowment_data)
     print(plt) })

crossref %>% 
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  group_by(name) %>%
  mutate(count_na = sum(is.na(value)),
            count_not_na = sum(!is.na(value))) %>%
  ungroup() %>%
  ggplot(aes(x = value)) +
 # geom_boxplot() +
  geom_histogram(bins = 50)

crossref %>% 
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  group_by(name) %>%
  mutate(count_na = sum(is.na(value)),
            count_not_na = sum(!is.na(value))) %>%
  ungroup() %>%
  ggplot(aes(x = name, y=value)) +
  geom_boxplot() +
  geom_jitter(alpha = .5, height = 0, width = .01)

crossref %>% 
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  group_by(name) %>%
  mutate(count_na = sum(is.na(value)),
            count_not_na = sum(!is.na(value))) %>%
  ungroup() %>%
  ggplot(aes(x = name, y = value)) +
 # geom_boxplot() +
  geom_density()



  geom_histogram() +
  facet_wrap(~name) +
  scale_x_log10()